﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Diagnostics;
// TODO:   按照以下步骤启用功能区(XML)项:

// 1. 将以下代码块复制到 ThisAddin、ThisWorkbook 或 ThisDocument 类中。

//  protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
//  {
//      return new YouJian();
//  }

// 2. 在此类的“功能区回调”区域中创建回调方法，以处理用户
//    操作(如单击某个按钮)。注意: 如果已经从功能区设计器中导出此功能区，
//    则将事件处理程序中的代码移动到回调方法并修改该代码以用于
//    功能区扩展性(RibbonX)编程模型。

// 3. 向功能区 XML 文件中的控制标记分配特性，以标识代码中的相应回调方法。  

// 有关详细信息，请参见 Visual Studio Tools for Office 帮助中的功能区 XML 文档。


namespace 光谱分析仪自动获取
{
    [ComVisible(true)]
    public class YouJian : Office.IRibbonExtensibility
    {
        private Office.IRibbonUI ribbon;
        static string connectString = "server=10.0.2.136;database=DevData;uid=sa;pwd=Ql-mes-2021;Max Pool Size=3000;";
        public YouJian()
        {
        }
        public void OnMyButtonClick(Office.IRibbonControl control)
        {
            Excel.Application excelApp = Globals.ThisAddIn.Application;
            Excel.Range selectedRange = excelApp.Selection as Excel.Range;

            if (selectedRange == null)
            {
                MessageBox.Show("请先选择单元格区域！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            // 条件1：检查是否正好是D、E、F列
            if (selectedRange.Column != 4 || selectedRange.Columns.Count != 3) // D=4, 且必须3列
            {
                MessageBox.Show("必须选择 D、E、F 三列，不能多也不能少！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            // 条件2：检查行数是否≤100
            if (selectedRange.Rows.Count > 100)
            {
                MessageBox.Show("选中的行数不能超过100行！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            // 处理每一行
            ProcessSelectedRows(selectedRange);
        }

        private void ProcessSelectedRows(Excel.Range selectedRange)
        {
            Excel.Worksheet worksheet = selectedRange.Worksheet;
            int startRow = selectedRange.Row;
            int endRow = startRow + selectedRange.Rows.Count - 1;
            Random random = new Random();

            // 关闭屏幕刷新（提升性能）
            Excel.Application excelApp = Globals.ThisAddIn.Application;
            excelApp.ScreenUpdating = false;

            try
            {
                for (int row = startRow; row <= endRow; row++)
                {
                    try
                    {
                        // 1. 获取查询条件（处理空值情况）2
                        string pihao = GetCellValue(worksheet, row, 4);
                        string mubanhao = GetCellValue(worksheet, row, 5);
                        string tongci = GetCellValue(worksheet, row, 6);

                        if (string.IsNullOrWhiteSpace(pihao)
                            || string.IsNullOrWhiteSpace(mubanhao)
                            || string.IsNullOrWhiteSpace(tongci))
                        {
                            continue; // 跳过无效行
                        }

                        // 2. 执行SQL查询
                        string sql = string.Empty;
                        if (tongci.ToUpper().StartsWith("R"))
                        {
                            sql = $@"SELECT TOP (1)  DevName, ReportCreateTime, PinMing, LvCi, Result, 
                    C, Si, Mn, P, S, Cr, Mo, Ni, Al, Co, Cu, Nb, Ti, V, W, Pb, Sn, Mg, 
                    [AS] , Zr, Bi, Ce, Sb, Se, Te, B, Zn, La, Fe, Ceq, Ta, Ca FROM dbo.GpfxyPro WHERE PiHao='{pihao}' AND MuBanHao='{mubanhao}' AND TongCi LIKE 'R%'  ORDER BY ReportCreateTime DESC";
                        }
                        else
                        {
                            sql = $@"SELECT TOP (1) DevName, ReportCreateTime, PinMing, LvCi, Result, 
                    C, Si, Mn, P, S, Cr, Mo, Ni, Al, Co, Cu, Nb, Ti, V, W, Pb, Sn, Mg, 
                    [AS] , Zr, Bi, Ce, Sb, Se, Te, B, Zn, La, Fe, Ceq, Ta, Ca
                    FROM dbo.GpfxyPro
                    WHERE PiHao = '{pihao}'
                    AND MuBanHao = '{mubanhao}'
                    AND TongCi = '{tongci}'
                    ORDER BY ReportCreateTime DESC;";
                        }
                      

                        var res = DbHelper.ExecuteDataset(connectString, System.Data.CommandType.Text, sql);

                        // 3. 处理查询结果
                        if (res == null || res.Tables.Count == 0 || res.Tables[0].Rows.Count == 0)
                        {
                            continue;
                        }

                        System.Data.DataRow rowdata = res.Tables[0].Rows[0];

                        // 4. 填充Excel数据（带异常处理）
                        SafeSetCellValue(worksheet, row, 1, () =>
                            rowdata.Field<DateTime>("ReportCreateTime").ToString("yyyy/M/d"));

                        //SafeSetCellValue(worksheet, row, 2, () => DateTime.Now.ToString("HH:mm:ss"));

                        SafeSetCellValue(worksheet, row, 3, () =>
                        {
                            string pinMing = rowdata.Field<string>("PinMing") ?? "";   
                            string result = pinMing.Split(new[] { "-R", "-J" }, StringSplitOptions.None)[0];   

                            return string.IsNullOrWhiteSpace(GetCellValue(worksheet, row, 3))
                                ? result
                                : worksheet.Cells[row, 3].Value2.ToString();
                        });
                        //截取rowdata.Field<string>("PinMing")中"-R"或者"-J"之前的数据
                        SafeSetCellValue(worksheet, row, 7, () =>
                            string.IsNullOrWhiteSpace(GetCellValue(worksheet, row, 7))
                                ? rowdata.Field<string>("LvCi")
                                : worksheet.Cells[row, 7].Value2.ToString());

                        SafeSetCellValue(worksheet, row, 8, () =>
                            string.IsNullOrWhiteSpace(GetCellValue(worksheet, row, 8))
                                ? rowdata.Field<string>("DevName")
                                : worksheet.Cells[row, 8].Value2.ToString());

                        SafeSetCellValue(worksheet, row, 41, () =>
                            string.IsNullOrWhiteSpace(GetCellValue(worksheet, row, 41))
                                ? rowdata.Field<string>("Result").ToUpper()=="TRUE"?"OK":"NG"
                                : worksheet.Cells[row, 41].Value2.ToString());
                        // 5. 填充所有元素数据
                        Dictionary<int, string> elementColumns = new Dictionary<int, string>()
                {
                    {12, "C"}, {13, "Si"}, {14, "Mn"}, {15, "P"}, {16, "S"},
                    {17, "Cr"}, {18, "Mo"}, {19, "Ni"}, {20, "Al"}, {21, "Co"},
                    {22, "Cu"}, {23, "Nb"}, {24, "Ti"}, {25, "V"}, {26, "W"},
                    {27, "Pb"}, {28, "Sn"}, {29, "Mg"}, {30, "As"}, {31, "Zr"},
                    {32, "Bi"}, {33, "Ce"}, {34, "Sb"}, {35, "Se"}, {36, "Te"},
                    {37, "B"}, {38, "Zn"}, {39, "La"}, {40, "Fe"}
                };

                        foreach (var col in elementColumns)
                        {
                            //SafeSetCellValue(worksheet, row, col.Key, () =>
                            //    string.IsNullOrWhiteSpace(GetCellValue(worksheet, row, col.Key))
                            //        ? rowdata.Field<double>(col.Value)
                            //        : worksheet.Cells[row, col.Key].Value2.ToString());
                            worksheet.Cells[row, col.Key].Value2 = rowdata.Field<double>(col.Value);
                        }


                    }
                    catch (Exception ex)
                    {
                        // 记录错误但继续处理下一行
                        //Debug.WriteLine($"处理第 {row} 行时出错: {ex.Message}");
                        MessageBox.Show($"处理第 {row} 行时出错: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        continue;
                    }
                }
            }
            finally
            {
                // 恢复屏幕刷新
                excelApp.ScreenUpdating = true;
            }

            //MessageBox.Show($"已处理 {endRow - startRow + 1} 行数据！",
            //               "完成",
            //               MessageBoxButtons.OK,
            //               MessageBoxIcon.Information);
        }

        // 辅助方法：安全获取单元格值
        private string GetCellValue(Excel.Worksheet worksheet, int row, int col)
        {
            try
            {
                return worksheet.Cells[row, col].Value2?.ToString().Trim() ?? string.Empty;
            }
            catch
            {
                return string.Empty;
            }
        }

        // 辅助方法：安全设置单元格值
        private void SafeSetCellValue(Excel.Worksheet worksheet, int row, int col, Func<object> valueProvider)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(GetCellValue(worksheet, row, col)))
                {
                    worksheet.Cells[row, col].Value2 = valueProvider();
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"设置单元格({row},{col})失败: {ex.Message}");
            }
        }





        #region IRibbonExtensibility 成员

        public string GetCustomUI(string ribbonID)
        {
            return GetResourceText("光谱分析仪自动获取.YouJian.xml");
        }

        #endregion

        #region 功能区回调
        //在此处创建回叫方法。有关添加回叫方法的详细信息，请访问 https://go.microsoft.com/fwlink/?LinkID=271226

        public void Ribbon_Load(Office.IRibbonUI ribbonUI)
        {
            this.ribbon = ribbonUI;
        }

        #endregion

        #region 帮助器

        private static string GetResourceText(string resourceName)
        {
            Assembly asm = Assembly.GetExecutingAssembly();
            string[] resourceNames = asm.GetManifestResourceNames();
            for (int i = 0; i < resourceNames.Length; ++i)
            {
                if (string.Compare(resourceName, resourceNames[i], StringComparison.OrdinalIgnoreCase) == 0)
                {
                    using (StreamReader resourceReader = new StreamReader(asm.GetManifestResourceStream(resourceNames[i])))
                    {
                        if (resourceReader != null)
                        {
                            return resourceReader.ReadToEnd();
                        }
                    }
                }
            }
            return null;
        }

        #endregion
    }
}
